#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2024/7/26 17:38
# @Author  : 王凯
# @File    : update_ths_redis_company_info.py
# @Project : scrapy_spider
import datetime
import json

import cpca

from utils.db.redisdb import RedisDB
from utils.logs import logger
from utils.tonghuashun_tools import PostgresqlDB
from utils.tools import format_sql_value, list2str


def make_update_pg_sql(table, data, condition):
    key_values = []

    for key, value in data.items():
        value = format_sql_value(value)
        if isinstance(value, str):
            key_values.append("{}={}".format(key, repr(value)))
        elif value is None:
            key_values.append("{}={}".format(key, "null"))
        else:
            key_values.append("{}={}".format(key, value))

    key_values = ", ".join(key_values)

    sql = "update {table} set {key_values} where {condition}"
    sql = sql.format(table=table, key_values=key_values, condition=condition)
    return sql


def make_insert_pg_sql(table, data, auto_update=False, update_columns=(), insert_ignore=False):
    keys = ["{}".format(key) for key in data.keys()]
    keys = list2str(keys).replace("'", "")

    values = [format_sql_value(value) for value in data.values()]
    values = list2str(values)

    if update_columns:
        if not isinstance(update_columns, (tuple, list)):
            update_columns = [update_columns]
        update_columns_ = ", ".join(["{key}=EXCLUDED.{key}".format(key=key) for key in update_columns])
        sql = "insert%s into {table} {keys} values {values} ON CONFLICT (seq) DO UPDATE SET %s" % (
            " ignore" if insert_ignore else "",
            update_columns_,
        )

    elif auto_update:
        sql = "replace into `{table}` {keys} values {values}"
    else:
        sql = "insert%s into `{table}` {keys} values {values}" % (" ignore" if insert_ignore else "")

    sql = sql.format(table=table, keys=keys, values=values).replace("None", "null")
    return sql


def parse_region_info(addresses):
    """解析地址信息"""
    return_ret = []
    for addr in addresses:
        try:
            region_list = cpca.transform([addr]).values.tolist()
            if region_list[0] and region_list[0][0]:
                return_ret = region_list[0][:3]
                break
        except:
            pass
    return return_ret


class UpdateThsRedisCompanyInfo(object):

    def __init__(self, result):
        self.result = result
        self._pg_db = None
        self._redis_db = None
        self.query_key = result['base_info']['taxpayer_id']

    @property
    def pg_db(self):
        if self._pg_db is None:
            TONGHUASHUN_CONFIG = {
                "ip": "server-forward.local.wfq2020.com",
                "db": "tonghuashun-datasync",
                "port": 55432,
                "user_name": "p.third-party-api.p.v1",
                "user_pass": "ba914871062365732c5eacf1c61daffd",
            }
            self._pg_db = PostgresqlDB(
                ip=TONGHUASHUN_CONFIG["ip"],
                port=TONGHUASHUN_CONFIG["port"],
                db=TONGHUASHUN_CONFIG["db"],
                user_name=TONGHUASHUN_CONFIG["user_name"],
                user_pass=TONGHUASHUN_CONFIG["user_pass"],
            )
        return self._pg_db

    @property
    def redis_client(self):
        if self._redis_db is None:
            self._redis_db = RedisDB(url='redis://:8461e2c92b2848975c058cb4fbba735f@server-forward.local.wfq2020.com:56382/1')
            # self._redis_db = RedisDB()
        return self._redis_db

    @staticmethod
    def date_format(date_str):
        if date_str:
            d = datetime.datetime.strptime(date_str, "%Y年%m月%d日")
            return str(d.date())
        else:
            return date_str

    def get_next_new_seq(self, key, _model_cls, redis_client):
        if redis_client.exists(key):
            ret = int(int(redis_client.incr(key)))
            return ret
        else:
            sql = f"select max(seq) as max_seq  from {_model_cls}"
            datas = self.pg_db.find(sql, to_json=True)
            if datas:
                max_seq = int(datas[0]["max_seq"])
                redis_client.set(key, max_seq)
            return int(int(redis_client.incr(key)))

    def get_max_seq(self, model_cls="enterprise_basic_info"):
        if model_cls == "individual_business_info":
            return self.get_next_new_seq("max_seq_ind", "individual_business_info", self.redis_client)
        return self.get_next_new_seq("max_seq_ent", "enterprise_basic_info", self.redis_client)

    def save_base_info_to_ths(self, base_info):
        region_list = parse_region_info([base_info["register_address"], base_info["belong_org"]])
        base_info["province"] = region_list[0]
        base_info["city"] = region_list[1] if region_list else ""
        base_info["district"] = region_list[2] if region_list else ""
        base_info["district_encode"] = list(
            cpca._extract_addrs(
                base_info["province"] + (base_info["city"] or '') + (base_info["district"] or ''), pos_sensitive=False, umap={}
            )
        )[0]["adcode"]

        if "个体" in base_info.get("organization_type"):
            save_dict = dict(
                seq=self.get_max_seq("individual_business_info"),
                isvalid=1,
                corp_name=base_info.get("company_name"),
                province=base_info.get("province"),
                legal_representative=base_info.get("legal_name"),
                reg_num=base_info.get("register_id"),
                corp_type=base_info.get("organization_type"),
                corp_address=base_info.get("register_address"),
                established_date=self.date_format(base_info.get("start_date")),
                operating_from_time=self.date_format(base_info.get("start_date")),
                operating_to_time=self.date_format(base_info.get("end_operation_date")),
                operating_scope=base_info.get("business_scope"),
                record_authority=base_info.get("belong_org"),
                approval_date=self.date_format(base_info.get("approval_date")),
                operating_status=base_info.get("operating_status"),
                reg_capital=base_info.get("register_capital") / 10000 if base_info.get("register_capital") else "",
                reg_capital_pub=base_info.get("register_capital_msg"),
                approval_authority=base_info.get("belong_org"),
                unified_social_credit_code=base_info.get("taxpayer_id"),
                taxpayer_ident_number=base_info.get("taxpayer_id"),
                district_encode=base_info.get("district_encode"),
                city=base_info.get("city"),
                used_name=",".join(base_info.get("history_names")),
                currency_name="人民币",
                operating_dur=f'{base_info.get("start_date")}至{base_info.get("end_operation_date") or "无固定期限"}'
                if base_info.get("start_date")
                else "",
            )
            obj = self.pg_db.find(f"select * from individual_business_info where unified_social_credit_code='{base_info.get('taxpayer_id')}' and isvalid = 1", to_json=True)
            if obj:
                obj_dict = obj[0]
                for k, v in save_dict.items():
                    obj_dict[k] = v
                obj_dict.pop("sys_update_time", None)
                self.pg_db.update(make_update_pg_sql("individual_business_info", obj_dict, condition=f"seq={obj_dict['seq']}").replace('`', ' '))
            else:
                save_dict.pop("sys_update_time", None)
                self.pg_db.add(make_insert_pg_sql("individual_business_info", save_dict, update_columns=[i for i in save_dict.keys() if i != "seq"]).replace('`', ' '))
        else:
            save_dict = dict(
                seq=self.get_max_seq("enterprise_basic_info"),
                isvalid=1,
                update_date=self.date_format(base_info.get("check_date")),
                corp_name=base_info.get("company_name"),
                unified_social_credit_code=base_info.get("taxpayer_id"),
                reg_num=base_info.get("register_id"),
                operating_status=base_info.get("operating_status"),
                corp_type=base_info.get("organization_type"),
                established_date=self.date_format(base_info.get("start_date")),
                legal_representative=base_info.get("legal_name"),
                reg_capital=base_info.get("register_capital") / 10000 if base_info.get("register_capital") else "",
                currency_name="人民币",
                operating_dur=f'{base_info.get("start_date")}至{base_info.get("end_operation_date") or "无固定期限"}'
                if base_info.get("start_date")
                else "",
                record_authority=base_info.get("belong_org"),
                issue_date=self.date_format(base_info.get("start_date")),
                corp_address=base_info.get("register_address"),
                operating_scope=base_info.get("business_scope"),
                is_latest=1,
                used_name=",".join(base_info.get("history_names")),
                province=base_info.get("province"),
                city=base_info.get("city"),
                district_encode=base_info.get("district_encode") or "",
                reg_capital_pub=base_info.get("register_capital_msg"),
                operating_status_not_pub=base_info.get("operating_status"),
                tax_id=base_info.get("taxpayer_id"),
            )
            obj = self.pg_db.find(f"select * from enterprise_basic_info where unified_social_credit_code='{base_info.get('taxpayer_id')}' and isvalid = 1", to_json=True)
            if obj:
                obj_dict = obj[0]
                for k, v in save_dict.items():
                    if k == "seq":
                        continue
                    obj_dict[k] = v
                obj_dict.pop("sys_update_time", None)
                self.pg_db.update(make_update_pg_sql("enterprise_basic_info", obj_dict, condition=f"seq={obj_dict['seq']}").replace('`', ' '))
            else:
                save_dict.pop("sys_update_time", None)
                self.pg_db.add(make_insert_pg_sql("enterprise_basic_info", save_dict, update_columns=[i for i in save_dict.keys() if i != "seq"]).replace('`', ' '))

    def save_base_info_to_qxb_redis(self, base_info):
        redis_key = f"weifengqi:integration:thirdparty:qixinbao:BasicInfo:[('keyword', '{self.query_key}')]"
        region_list = parse_region_info([base_info["register_address"], base_info["belong_org"]])
        base_info["province"] = region_list[0]
        base_info["city"] = region_list[1] if region_list else ""
        base_info["district"] = region_list[2] if region_list else ""
        base_info["district_encode"] = list(
            cpca._extract_addrs(
                base_info["province"] + (base_info["city"] or '') + (base_info["district"] or ''), pos_sensitive=False, umap={}
            )
        )[0]["adcode"]

        save_dict = {
            "status": "200",
            "message": "操作成功",
            "sign": "",
            "data": {
                "id": "",
                "name": base_info.get('company_name'),
                "format_name": base_info.get('company_name'),
                "econKind": base_info.get('organization_type'),
                "econKindCode": "1151",
                "registCapi": f"{base_info.get('register_capital') / 10000} 万人民币" if base_info.get('register_capital') else None,
                "currency_unit": "CNY",
                "type_new": "01",
                "historyNames": base_info.get('history_names'),
                "address": base_info.get('register_address'),
                "regNo": base_info.get('register_id'),
                "scope": base_info.get('business_scope'),
                "termStart": self.date_format(base_info.get('start_date')),
                "termEnd": self.date_format(base_info.get('end_operation_date')),
                "belongOrg": base_info.get('belong_org'),
                "operName": base_info.get('legal_name'),
                "title": "法定代表人",
                "startDate": self.date_format(base_info.get('start_date')),
                "endDate": self.date_format(base_info.get('end_operation_date')),
                "checkDate": self.date_format(base_info.get('check_date')),
                "status": base_info.get('operating_status'),
                "new_status": base_info.get('operating_status'),
                "orgNo": "",
                "creditNo": base_info.get('taxpayer_id'),
                "districtCode": base_info.get('district_encode'),
                "actualCapi": "",
                "categoryNew": '',
                "domain": "",
                "tags": [],
                "revoke_reason": "-",
                "logout_reason": "-",
                "revoke_date": "-",
                "fenname": "-"
            }
        }
        self.redis_client.set(redis_key, json.dumps(save_dict, ensure_ascii=False))
        self.redis_client._redis.expire(redis_key, 60 * 60 * 24 * 30)

    def save_shareholder_to_qxb_redis(self, shareholder):
        redis_key = f"weifengqi:integration:thirdparty:qixinbao:Partners:[('keyword', '{self.query_key}'), ('skip', 0)]"
        save_dict = {
            "status": "200",
            "message": "操作成功",
            "sign": "",
            "data": {
                "total": len(shareholder),
                "items": [{
                    "name": one['inv'],
                    "stockType": one['invType_CN'],
                    "identifyType": "非公示项",
                    "identifyNo": "-",
                    "stockPercent": f"{one['percent'] / 100}",
                    "totalRealCapi": f"{one['realCapi']}万人民币" if one.get('realCapi') else '-',
                    "totalShouldCapi": f"{one['shouldCapi']}万人民币",
                    "realCapiItems": [
                        {
                            "date": i['conDate'],
                            "capi": f"{i['subConAm']} 万人民币",
                            "type": i['conForm_CN']
                        } for i in (one.get('real') or [])
                    ],
                    "shouldCapiItems": [
                        {
                            "date": i['conDate'],
                            "capi": f"{i['subConAm']} 万人民币",
                            "type": i['conForm_CN']
                        } for i in (one.get('should') or [])
                    ]
                } for one in shareholder]
            }
        }
        self.redis_client.set(redis_key, json.dumps(save_dict, ensure_ascii=False))
        self.redis_client._redis.expire(redis_key, 60 * 60 * 24 * 30)

    def save_alter_info_to_qxb_db(self, alter_info):
        redis_key = f"weifengqi:integration:thirdparty:qixinbao:ChangeRecords:[('keyword', '{self.query_key}')]"
        save_dict = {
            "status": "200",
            "message": "操作成功",
            "sign": "",
            "data": {
                "total": 1,
                "items": [
                    {
                        "changeItem": one['altItem_CN'],
                        "changeDate": one['altDate'],
                        "beforeContent": one['altBe'] or '-',
                        "afterContent": one['altAf'] or '-',
                        "tag": "非历史信息",
                        "type": one['altItem_CN']
                    } for one in alter_info
                ]
            }
        }
        self.redis_client.set(redis_key, json.dumps(save_dict, ensure_ascii=False))
        self.redis_client._redis.expire(redis_key, 60 * 60 * 24 * 30)

    def get_max_seq_record(self, model_cls="change_record"):
        return self.get_next_new_seq("max_seq_change_records", model_cls, self.redis_client)

    def save_alter_info_to_ths_db(self, alter_info, base_info):
        self.pg_db.execute(f"update change_record set isvalid = 0 WHERE corp_name='{base_info['company_name']}' and isvalid = 1")

        for idx, one in enumerate(alter_info, start=1):
            item = dict(
                seq=self.get_max_seq_record("change_record"),
                isvalid=1,
                corp_name=base_info['company_name'],
                seq_num=idx,
                chg_project=one['altItem_CN'],
                chg_date=one['altDate'],
                pre_chg=one['altAf'],
                afterchg=one['altAf'],
            )
            self.pg_db.add(make_insert_pg_sql("change_record", item, update_columns=[i for i in item.keys() if i != "seq"]).replace('`', ' '))

    def run(self):
        info_dict = self.result
        base_info = info_dict.get("base_info")  # 基础信息
        for k, v in base_info.items():
            if k in ['operating_status']:
                base_info[k] = (v or '').replace("（", "(").replace("）", ")")
        self.save_base_info_to_ths(base_info)
        self.save_base_info_to_qxb_redis(base_info)
        shareholder = info_dict.get("shareholder")  # 股东信息
        if shareholder:
            self.save_shareholder_to_qxb_redis(shareholder)
        alter_info = info_dict.get("alterInfo") or info_dict.get("gtAlertInfo")  # 变更信息
        self.save_alter_info_to_qxb_db(alter_info)
        self.save_alter_info_to_ths_db(alter_info, base_info=base_info)
        logger.info(f"{self.query_key} 更新完成")


redis_client = RedisDB(
    # url='redis://:c92b2848975c058cb4fbba735f@server-forward.local.wfq2020.com:56382/1',
    ip_ports='server-forward.local.wfq2020.com:56382',
    user_pass='8461e2c92b2848975c058cb4fbba735f',
    db=1,
)

if __name__ == '__main__':
    def update_status(taxpayer_id):
        redis_key = f"weifengqi:integration:thirdparty:qixinbao:AnnualReportSummary:[('keyword', '{taxpayer_id}')]"
        datas = redis_client.get(redis_key)
        data = json.loads(datas)
        list_data = data['data']['items']
        for i in list_data:
            partners = i['partners']
            for j in partners:
                should_capi_items = j['should_capi_items']
                for k in should_capi_items:
                    if '万' not in k['shoud_capi']:
                        k['shoud_capi'] = f"{k['shoud_capi']}万元"

                real_capi_items = j['real_capi_items']
                for v in real_capi_items:
                    if '万' not in v['real_capi']:
                        v['real_capi'] = f"{v['real_capi']}万元"
        redis_client.set(redis_key, json.dumps(data, ensure_ascii=False))
        redis_client._redis.expire(redis_key, 60 * 60 * 24 * 30)

        sql = f"delete `third_party_company_annual_report` where `taxpayer_id` = '{taxpayer_id}' and create_time > '{datetime.datetime.now().date() - datetime.timedelta(days=30)}'"
        print(sql)

    tax_ids = []
    for i in tax_ids:
        update_status(i)
